package com.yzk.auction.dao;

import com.yzk.auction.entity.FirstCategory;
import com.yzk.auction.entity.Goods;
import com.yzk.auction.entity.GoodsDTO;
import com.yzk.auction.entity.SecondCategory;
import com.yzk.auction.utils.JDBCUtils;
import com.yzk.auction.utils.PageModel;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * Author: 园中葵小组成员
 * Description:商品相关数据交互
 */
public class GoodsDao {
    private DataSource dataSource = JDBCUtils.getDataSource();
    private GoodsDTO goodsDTO = null;
    /**
     * 更新商品信息
     * @param num
     * @param price
     * @param goods_id
     * @throws SQLException
     */
    public void  undateGoodsNum(int num, double price, int goods_id) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
        runner.update("UPDATE tb_goods set auction_times=?,end_price=? where goods_id=?",
                num,price,goods_id);
    }
    /**
     * 分页查询油画数据
     * @param page 页号
     * @param rows 每页记录数
     * @return PageModel分页对象
     * */
    public PageModel pagination(int page, int rows) {
        QueryRunner qr = new QueryRunner(dataSource);
        String sql = "select goods_id, goods_name, g.second_cid as second_cid, end_time, low_price, auction_times, start_time, end_price, goods_picture, goods_desc, price_markup, appraisal_price, second_cname, s.first_cid as first_cid, first_cname from tb_goods g,tb_second_category s,tb_first_category f where g.second_cid = s.second_cid and s.first_cid = f.first_cid";
            PageModel pageModel = null;
            try {
            List<GoodsDTO> list = qr.query(sql,new BeanListHandler<>(GoodsDTO.class),new Object[]{});

            //PageModel分页处理得到分页数据及分页附加
            pageModel = new PageModel(list,page,rows);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return pageModel;
    }
    /**
     * 查询油画数据
     * @param
     * @param
     * @return GoodsDTO商品详情对象
     * */
    public static List<GoodsDTO> query() {

        DataSource dataSource = JDBCUtils.getDataSource();
        QueryRunner qr = new QueryRunner(dataSource);
        String sql = "select goods_id, goods_name, g.second_cid as second_cid, end_time, low_price, auction_times, start_time, end_price, goods_picture, goods_desc, price_markup, appraisal_price, second_cname, s.first_cid as first_cid, first_cname from tb_goods g,tb_second_category s,tb_first_category f where g.second_cid = s.second_cid and s.first_cid = f.first_cid";
        List<GoodsDTO> list = null;
        try {
            list = qr.query(sql,new BeanListHandler<>(GoodsDTO.class),new Object[]{});

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    /**
     * 根据Id删除商品信息
     * @param id
     */
    public void delete(Integer id){
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            QueryRunner qr = new QueryRunner();
            conn.setAutoCommit(false);
            String sql = "delete from tb_goods where goods_id = ?";
            qr.update(conn,sql,new Object[]{id});
            conn.commit();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            try {
                if(conn!=null && !conn.isClosed()){
                    conn.rollback();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    /**
     * 查询所有一级分类
     * @return 一级分类的list集合
     */
    public List<FirstCategory> findAllFirstCategory(){
        QueryRunner qr = new QueryRunner(dataSource);
        String sql = "select * from tb_first_category";
        List<FirstCategory> list = null;
        try {
            list = qr.query(sql,new BeanListHandler<>(FirstCategory.class),new Object[]{});
            for (FirstCategory f:list){
                System.out.println(f);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    /**
     * 查询所有二级分类
     * @return 二级分类的list集合
     */
    public List<SecondCategory> findAllSecondCategory(){
        QueryRunner qr = new QueryRunner(dataSource);
        String sql = "select * from tb_second_category";
        List<SecondCategory> list = null;
        try {
            list = qr.query(sql,new BeanListHandler<>(SecondCategory.class),new Object[]{});
            for (SecondCategory s:list){
                System.out.println(s);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    /**
     * 根据二级分类名查询数据，并分页显示
     * @param second_cid 一级分类id
     * @param page 页号
     * @param rows 每页记录数
     * @return PageModel分页对象
     */
    public PageModel paginationsecondid(String second_cid,int page, int rows) {
        QueryRunner runner = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select goods_id, goods_name, g.second_cid as second_cid, end_time, low_price, auction_times, start_time, end_price, goods_picture, goods_desc, price_markup, appraisal_price, second_cname, s.first_cid as first_cid, first_cname from tb_goods g,tb_second_category s,tb_first_category f where g.second_cid = s.second_cid and s.first_cid = f.first_cid and s.second_cname=?;";
        PageModel pageModel = null;
        try {
            List<GoodsDTO> list = runner.query(sql,new BeanListHandler<GoodsDTO>(GoodsDTO.class),second_cid);
            pageModel = new PageModel(list,page,rows);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pageModel;
    }

    /**
     * 首页模糊查询
     * @param like 索引
     * @param page 页号
     * @param rows 每页记录数
     * @return PageModel分页对象
     */
    public PageModel paginationLike(String like, int page, int rows){
        QueryRunner queryRunner = new QueryRunner(dataSource);
        PageModel pageModel = null;
        try {
            List<GoodsDTO> likeList = queryRunner.query("SELECT * FROM tb_goods WHERE goods_name LIKE ?", new BeanListHandler<GoodsDTO>(GoodsDTO.class), "%" + like + "%");
            pageModel = new PageModel(likeList, page, rows);
            return pageModel;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
    }
    /**Y
     * 根据一级分类id获取二级分类
     * @return 二级分类的list集合
     */
    public List<SecondCategory> findSecondById(Integer id){
        QueryRunner qr = new QueryRunner(dataSource);
        String sql = "select * from tb_second_category where first_cid = ?";
        List<SecondCategory> list = null;
        try {
            list = qr.query(sql,new BeanListHandler<>(SecondCategory.class),new Object[]{id});
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
    /**Y
     * 添加商品
     * @param goodsDTO
     * @return 添加成功影响的行数
     */
    public int createGoods(GoodsDTO goodsDTO){
        QueryRunner qr = new QueryRunner(dataSource);
        String sql = "INSERT INTO tb_goods(goods_name, second_cid, end_time, low_price, start_time, goods_picture, goods_desc, price_markup, appraisal_price) " +
                "VALUES (?,?,?,?,?,?,?,?,?)";
        int num = 0;

        long start = goodsDTO.getStart_time().getTime();//获取自1970到现在的毫秒数
        java.sql.Timestamp startTime = new java.sql.Timestamp(start);
        //java.util.Data转为java.sql.Date
        long end = goodsDTO.getEnd_time().getTime();
        java.sql.Timestamp endTime = new java.sql.Timestamp(end);
        try {
            num = qr.update(sql,new Object[]{goodsDTO.getGoods_name(),goodsDTO.getSecond_cid(),endTime,goodsDTO.getLow_price(),startTime,goodsDTO.getGoods_picture(),goodsDTO.getGoods_desc(),goodsDTO.getPrice_markup(),goodsDTO.getAppraisal_price()});
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return num;
    }
    /**Y
     * 根据商品id查询商品信息
     * @param id
     * @return 商品扩展实体对象
     */
    public GoodsDTO findGoodsById(Integer id){
        QueryRunner qr = new QueryRunner(dataSource);
        String sql = "select goods_id, goods_name, g.second_cid as second_cid, end_time, low_price, auction_times, start_time, end_price, goods_picture, goods_desc, price_markup, appraisal_price, second_cname, s.first_cid as first_cid, first_cname " +
                "from tb_goods g,tb_second_category s,tb_first_category f " +
                "where g.second_cid = s.second_cid and s.first_cid = f.first_cid and goods_id = ?";
        GoodsDTO goodsDTO = null;
        try {
            goodsDTO = qr.query(sql,new BeanHandler<>(GoodsDTO.class),new Object[]{id});
            System.out.println(goodsDTO);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return goodsDTO;
    }

    /**Y
     * 更新
     * @param goodsDTO
     */
    public void gupdate(GoodsDTO goodsDTO) {
        QueryRunner qr = new QueryRunner(dataSource);
        String sql = "UPDATE tb_goods SET goods_name=?,second_cid=?,start_time=?,end_time=?,low_price=?,price_markup=?,appraisal_price=?,goods_desc=?,goods_picture=?" +
                "WHERE goods_id=?";
        try {
            qr.update(sql,new Object[]{goodsDTO.getGoods_name(),goodsDTO.getSecond_cid(),goodsDTO.getStart_time(),goodsDTO.getEnd_time(),goodsDTO.getLow_price(),goodsDTO.getPrice_markup(),goodsDTO.getAppraisal_price(),goodsDTO.getGoods_desc(),goodsDTO.getGoods_picture(),goodsDTO.getGoods_id()});
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}